DROP VIEW IF EXISTS `bigclass`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `bigclass` AS select `open_category`.`cat_id` AS `cat_id`,`open_category`.`cat_name` AS `cat_name`,`open_category`.`parent_id` AS `parent_id` from `open_category` where (`open_category`.`parent_id` = '0');

DROP VIEW IF EXISTS `open_vt_cwfx`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `open_vt_cwfx` AS 
select (goods_amount-discount+tax+shipping_fee+pay_fee+pack_fee+card_fee - bonus)/goods_amount as zk,(goods_amount-discount+tax+shipping_fee+pay_fee+pack_fee+card_fee - bonus) as JSE , shipping_status,`goods_number`,`open_order_info`.`order_sn` AS `order_sn`,`open_order_info`.`pay_name` AS `pay_name`,`open_order_info`.`pay_id` AS `pay_id`,`open_order_info`.`shipping_name` AS `shipping_name`,
`open_order_info`.`shipping_id` AS `shipping_id`,`open_order_goods`.`goods_sn` AS `goods_sn`,`open_order_goods`.`goods_name` AS `goods_name`,`open_order_goods`.`goods_price` AS `goods_price`,`open_color`.`color_code` AS `color_code`,`open_color`.`color_name` AS `color_name`,
`open_size`.`size_code` AS `size_code`,`open_size`.`size_name` AS `size_name`,`open_order_info`.`goods_amount` AS `goods_amount`,`open_order_info`.`shipping_fee` AS `shipping_fee`,`open_order_info`.`pay_fee` AS `pay_fee`,`open_order_info`.`pack_fee` AS `pack_fee`,
`open_order_info`.`card_fee` AS `card_fee`,`open_order_info`.`money_paid` AS `money_paid`,`open_order_info`.`surplus` AS `surplus`,`open_order_info`.`bonus` AS `bonus`,`open_order_info`.`order_amount` AS `order_amount`,`open_order_info`.`add_time` AS `add_time` from (((`open_order_goods` left join `open_order_info` on((`open_order_goods`.`order_id` = `open_order_info`.`order_id`))) left join `open_color` on((`open_order_goods`.`color_id` = `open_color`.`color_id`))) left join `open_size` on((`open_order_goods`.`size_id` = `open_size`.`size_id`)))
 where open_order_info.shipping_status='1' ; 
 
DROP VIEW IF EXISTS `open_vt_spkc`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `open_vt_spkc` AS select `open_goods`.`goods_sn` AS `goods_sn`,`open_goods`.`goods_name` AS `goods_name`,`open_color`.`color_name` AS `color_name`,`open_color`.`color_code` AS `color_code`,`open_size`.`size_name` AS `size_name`,`open_size`.`size_code` AS `size_code`,`open_goods_inventory`.`goods_number_actual` AS `goods_number_actual`,`open_goods_inventory`.`goods_number_available` AS `goods_number_available` from (((`open_goods_inventory` left join `open_goods` on((`open_goods`.`goods_id` = `open_goods_inventory`.`goods_id`))) left join `open_color` on((`open_color`.`color_id` = `open_goods_inventory`.`color_id`))) left join `open_size` on((`open_size`.`size_id` = `open_goods_inventory`.`size_id`)));

DROP VIEW IF EXISTS open_vt_kffx;
create view open_vt_kffx as select  user_name,case  sex when 0 then '女'  when 1 then '男'  END  as sex  ,reg_time,mobile_phone,home_phone,nick_name,user_money,frozen_money,pay_points,rank_points ,order_sn,add_time,shipping_name,pay_name,referer ,(goods_amount-discount+tax+shipping_fee+pay_fee+pack_fee+card_fee - bonus)  as jse,action_user, case  open_order_info.order_status  when 1 then '确认'  when 4 then '退货'  when 7 then '完成' END as order_status from open_users left join open_order_info on  open_users.user_id=open_order_info.user_id left join open_order_action on  open_order_action.order_id = open_order_info.order_id  where open_order_action.shipping_status=1 and ( open_order_info.order_status = 1 or  open_order_info.order_status = 4 or open_order_info.order_status = 7); 

